<?php
//include all support file
include_once ("../modconfig.php");
include_once ("$ROOT_CLASS_DIR/class_database.php");
include_once ("$ROOT_CLASS_DIR/class_common.php");
include_once ("$ROOT_CLASS_DIR/class_login.php");

require_once('OLEwriter.php');
require_once('BIFFwriter.php');
require_once('Worksheet.php');
require_once('Workbook.php');

global $DBCoins,$DBGeneral,$DBConnection, $RS,$Index, $a, $b;

$DBConnection = new mydb(DB_USER, DB_PASSWORD, ConsDB, DB_HOST);

function HeaderingExcel($filename){
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:attachment;filename=$filename");
    header("Expires:0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Pragma: public");
}

function GetFromDate () {
    if (GetParam("m","")=="AUDIT")
    list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,12,1,GetParam("y",""))));
    else
    list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
    return array($Day,$Month,$Year,$NMonth);
}

function GetLastPeriod ($Month, $Year) {
    list($LDay,$LMonth,$LYear,$LNMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,$Month,0,$Year)));
    return array($LMonth,$LYear,$LNMonth);
}

function GetLastBl() {
            global $DBConnection;
            list($Day,$Month,$Year,$NMonth) = GetFromDate();
            list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);

            //Balance Last Month

            //if ($LMonth==12) $modcondition = " awal";
            //Rama, (25 November 2009), Start
            if ($LMonth==12 && GetParam("m","")<>"AUDIT") $modcondition = " awal";
            //Rama, (25 November 2009), End
            else $modcondition = " aku$LMonth";
            $SQL = "SELECT sum($modcondition) FROM tblcoa WHERE ledger='$Year' AND skada<>'".Get_salrlytd()."' ";
            return $DBConnection->dbc->get_var($SQL);
}

function GridList () {
}

function Get_salrlytd() {
   global $DBConnection;
  $SQL = "SELECT salrlytd FROM tblprofile";
  return $DBConnection->dbc->get_var($SQL);
}

function BuildPeriod ($year, $month) {
  $arraymonth = array("","JANUARI","FEBRUARI","MARET","APRIL","MEI","JUNI","JULI","AGUSTUS","SEPTEMBER","OKTOBER","NOPEMBER","DESEMBER", "BULAN - 13");
  if (GetParam("m","")=="AUDIT") $month=13;
  return $arraymonth[$month].", $year";
}

$namafile = "Consolidated_Trial_Balance_".GetParam("office","")."_".$Month."_".$Year;

//http headers
HeaderingExcel($namafile);

//make a workbook
$workbook=new Workbook("-");

//make first worksheet
$fJudul=& $workbook->add_format();
$fJudul->set_bold();
$fJudul->set_size(14);

$fJudulKolom=& $workbook->add_format();
$fJudulKolom->set_bold();
$fJudulKolom->set_size(8);
$fJudulKolom->set_align('centre');
$fJudulKolom->set_border(1);

$fBesar=& $workbook->add_format();
$fBesar->set_size(20);

$fKutipan=& $workbook->add_format();
$fKutipan->set_align('right');
$fKutipan->set_italic();
$fKutipan->set_size(8);
$fKutipan->set_color('blue');

$fList=& $workbook->add_format();
$fList->set_size(8);

$fIsi=& $workbook->add_format();
$fIsi->set_border(1);

$fIsiTengah=& $workbook->add_format();
$fIsiTengah->set_border(1);
$fIsiTengah->set_align('centre');

$angka=& $workbook->add_format();
$angka->set_num_format('#,##0');
$angka->set_border(1);

$angka2=& $workbook->add_format();
$angka2->set_num_format('#,##0.00');
$angka2->set_border(1);

$angkatengah=& $workbook->add_format();
$angkatengah->set_num_format('#,##0');
$angkatengah->set_border(1);
$angkatengah->set_align('centre');

$tanggal=& $workbook->add_format();
$tanggal->set_num_format('D-MMM-YYYY');
$tanggal->set_border(1);


$worksheet1= & $workbook->add_worksheet("Consolidated_Trial_Balance");
//seting zoom size
//75 is 75% zoom size
$worksheet1->set_zoom(100);

//set portrait page
$worksheet1->set_portrait(100);

//set a4 paper size
$worksheet1->set_paper(9);

//set hide gridlines
$worksheet1->hide_gridlines();

//set print area
$worksheet1->print_area(0,0,15,5);

//set page header
$worksheet1->set_header("header",$margin=2);


//set column
/*
1   2   3   4   5   6   7   8   9   0   11  12
ktrasl  ledger  skada   namacc  tglbkt  nobkt   urut    uraian  noref   jumlah  debet   credit
6   6   10  25  12  15  6   25  15  15  15  15
*/

$worksheet1->set_column(1,1,7);// no
$worksheet1->set_column(2,2,6);// ktrasl
$worksheet1->set_column(3,3,6);// ledger
$worksheet1->set_column(4,4,10); // skada
$worksheet1->set_column(5,5,40); // namacc
$worksheet1->set_column(6,6,12); // tglbkt
$worksheet1->set_column(7,7,15); // nobkt
$worksheet1->set_column(8,8,6); // urut
$worksheet1->set_column(9,9,35); // uraian
$worksheet1->set_column(10,10,15); // noref
$worksheet1->set_column(11,11,15); // jumlah
$worksheet1->set_column(12,12,15); // debet
$worksheet1->set_column(13,13,15);  // credit

//------------------------------------------------------------header

if (GetParam("m","")=="AUDIT")
    $title='TRIAL BALANCE CONSOLIDATION (AUDIT)';
else
    $title='TRIAL BALANCE CONSOLIDATION';
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. MASAJI TATANAN CONTAINER';

list($Day,$Month,$Year,$NMonth) = GetFromDate();
list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);
            //Rama, (25 November 2009), Start
            if (GetParam("m","")=="AUDIT"){
                $LNMonth = "DECEMBER";
                $NMonth = "THIRTEEN MONTH";
            }
            //Rama, (25 November 2009), End
//Header
$worksheet1->write_string(1,1,$title,$fJudul);
$worksheet1->write_string(2,1,$perush,$fJudul);
if (GetParam("m","")=="AUDIT")
$worksheet1->write_string(3,1,"PERIOD: ".BuildPeriod (GetParam("y",""), 12),$fJudul);
else
$worksheet1->write_string(3,1,"PERIOD: ".BuildPeriod (GetParam("y",""), GetParam("m","")),$fJudul);



$worksheet1->write_string(4,1,"OFFICE CODE.",$fJudulKolom);
$worksheet1->write_string(4,2,"TR. ACCOUNT",$fJudulKolom);
$worksheet1->write_string(4,3,"BALANCE",$fJudulKolom);
$worksheet1->write_string(4,4,strtoupper($NMonth),$fJudulKolom);
$worksheet1->write_string(4,5,"",$fJudulKolom);
$worksheet1->write_string(4,6,"BALANCE",$fJudulKolom);

$worksheet1->write_string(5,1,"",$fJudulKolom);
$worksheet1->write_string(5,2,"",$fJudulKolom);
$worksheet1->write_string(5,3,strtoupper($LNMonth),$fJudulKolom);
$worksheet1->write_string(5,4,"DEBET",$fJudulKolom);
$worksheet1->write_string(5,5,"HREDIT",$fJudulKolom);
$worksheet1->write_string(5,6,strtoupper($NMonth),$fJudulKolom);


// ----------------------------------------------------------isi data
// buat koneksi database dan cari data



            list($Day,$Month,$Year,$NMonth) = GetFromDate();
            list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);
            //Rama, 17 Nov 2009, Start
            $ledgerYear = substr($Year, 2);
            $ledgerYear = "$ledgerYear"."$Month";
            //Rama, 17 Nov 2009, End
            //Balance Last Month
                        //$TEMP = "TEMPORARY";

            //Rama, (25 November 2009), Start
            if(GetParam("m","")=="AUDIT") {
                list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month+1, $Year);
            }
            //Rama, (25 November 2009), End


            //if ($LMonth==12) $modcondition = " ,awal as lastbl ";
            //Rama, (25 November 2009), Start
            if ($LMonth==12  && GetParam("m","")<>"AUDIT") $modcondition = " ,awal as lastbl ";
            //Rama, (25 November 2009), End
            else $modcondition = " ,aku$LMonth as lastbl ";

                        $SQL = "DROP TABLE IF EXISTS `ais_tmpmemo`";
                        $DBConnection->dbc->query($SQL);

            $SQL =  "CREATE $TEMP TABLE `ais_tmpmemo` (".
                    "`skada` VARCHAR( 64 ) NOT NULL ,".
                    "`tglbkt` DATE NOT NULL ,".
                    "`debet` DECIMAL( 15, 2 ) NOT NULL ,".
                    "`credit` DECIMAL( 15, 2 ) NOT NULL,".
                    "`ktrasl` VARCHAR( 64 ) NOT NULL ) ";
            $DBConnection->dbc->query($SQL);
            //print $SQL;die();

                $SQL = "INSERT INTO `ais_tmpmemo` (skada, tglbkt, debet, credit, ktrasl)
                        SELECT skada, tglbkt, sum(IF(dk='D',rupiah,0)) as debet, sum(IF(dk='K',rupiah,0)) as credit, ktrasl
                        FROM ais_memorial
                        WHERE  MONTH(tglbkt)=".($Month*1)." AND YEAR(tglbkt)=$Year
                       ";

                if (GetParam("office",""))
                $SQL .= " AND ktrasl='".GetParam("office","")."'";

                if (GetParam("m","")=="AUDIT")
                $SQL .= " AND nobkt like 'MA%'";

                $SQL .= " GROUP BY ais_memorial.skada,ais_memorial.ktrasl";

            $DBConnection->dbc->query($SQL);
            //print $SQL;die();


            $SQL = "SELECT tblcoa.ktrasl, tblcoa.skada, tblcoa.namacc, ais_tmpmemo.debet as debet, ais_tmpmemo.credit as credit $modcondition
                    FROM tblcoa ";
                        if (!GetParam("office","")) {
                              $SQL .= "LEFT JOIN ais_tmpmemo ON tblcoa.skada=ais_tmpmemo.skada AND tblcoa.ktrasl=ais_tmpmemo.ktrasl ";
                        } else {
                              $SQL .= "LEFT JOIN ais_tmpmemo ON tblcoa.skada=ais_tmpmemo.skada ";
                        }
                        $SQL .= " WHERE 1 $WHERE AND tblcoa.skada<>'".Get_salrlytd()."' AND tblcoa.ledger='$Year' ";

                        if (GetParam("office","")) {
                              $SQL .= " AND tblcoa.ktrasl='".GetParam("office","")."'";
                        }
                        //$SQL .= " AND tblcoa.ktrasl='005'";

                        $SQL .= " ORDER BY tblcoa.ktrasl, tblcoa.skada";

            $MyResult = $DBConnection->dbc->get_results($SQL,ARRAY_A);
            //print $SQL;die();



$a=6;
if ($MyResult) {
    foreach ($MyResult as $row) {

        $totaldebet = $totaldebet + $row["debet"];
            $totalcredit = $totalcredit + $row["credit"];
            $totallastbl = $totallastbl + $row["lastbl"];
        if ($row["debet"]<>0 || $row["credit"]<>0 || $row["lastbl"]<>0) {
            $a = $a + 1;
                $worksheet1->write_string($a,1,$row["ktrasl"],$fIsi);
                $worksheet1->write_string($a,2,$row["skada"].' - '.$row["namacc"],$fIsi);
                $worksheet1->write_number($a,3,$row["lastbl"],$angka);
                $worksheet1->write_number($a,4,$row["debet"],$angka);
                $worksheet1->write_number($a,5,$row["credit"],$angka);
                //rama, 25 nov 2009, start
                    $Currmonth = (($row["lastbl"]+$row["debet"])-$row["credit"]);
                    //$GridString .= "\"".$Currmonth."\n";
                    $worksheet1->write_number($a,6,$Currmonth,$angka);
                //rama, 25 nov 2009, end
                //$worksheet1->write_number($a,6,$row["lastbl"]+$row["debet"]-$row["credit"],$angka);
        }

    }
    $worksheet1->write_string($a+1,2,"TOTAL",$fIsi);
    $worksheet1->write_number($a+1,3,GetLastBl(),$angka);
    $worksheet1->write_number($a+2,4,$totaldebet,$angka);
    $worksheet1->write_number($a+2,5,$totalcredit,$angka);
    $worksheet1->write_number($a+2,6,$totaldebet-$totalcredit,$angka);
}

$workbook->close();
?>
